<%@page language="java" import="java.sql.*"%>
<%@page language="java" import="java.util.*"%>

<HEAD>
	<TITLE>Multimedia Database: Search Results</TITLE>
</HEAD>

<BODY>
	<H1>Your search returned the following results:</H1>
</BODY>

<%
	Connection conn = null;
	
	try
	{
		//establishes the connection to the database
		Class.forName("com.mysql.jdbc.Driver").newInstance(); 
		conn = DriverManager.getConnection("jdbc:mysql://localhost/mmdb?user=root&password=mysqldbs"); 
		
		String searchType = request.getQueryString();
		
		String query = new String();
		
		Statement stmt = conn.createStatement();
		ResultSet rs;
		ResultSetMetaData rsmd;
		ResultSet temp;
		ResultSetMetaData tmd;
		int noCols = 0;
		int tCols = 0;
		
		//handles the simple search, if specified by the query string
		if (searchType.equals("simple"))
		{
			stmt = conn.createStatement();
			
			String cat = request.getParameter("category");
			String term = request.getParameter("term");
			
			if (cat.equals("Person"))
				query += "(SELECT P.Name, P.YrBorn, P.YrDied FROM PERSON P WHERE P.Name = \""+term+"\")";
			else if (cat.equals("Movie"))
				query = "SELECT med.Title, med.YrMade, med.Lang, mov.Profits FROM (SELECT * FROM MEDIA AS M WHERE M.Title	= \""+term+"\") AS med, MOVIE AS mov WHERE med.MedID = mov.MedID;";
			else if (cat.equals("TV Show"))
				query = "SELECT med.Title, med.YrMade, tv.YrEnded, tv.NoSeasons, tv.Network, med.Lang FROM (SELECT * FROM MEDIA AS M WHERE M.Title	= \""+term+"\") AS med, TVSHOW AS tv WHERE med.MedID = tv.MedID;";
			else if (cat.equals("Radio Show"))
				query = "SELECT med.Title, med.YrMade, rad.YrEnded, rad.Station, med.Lang FROM (SELECT * FROM MEDIA AS M WHERE M.Title	= \""+term+"\") AS med, RADIOSHOW AS rad WHERE med.MedID = rad.MedID;";
			else if (cat.equals("Character"))
				query = "SELECT p.Name, m.Title, pc.PART FROM(SELECT * FROM PLAYEDCHARACTER P WHERE P.Part = \""+term+"\") AS pc, PERSON AS p, MEDIA AS m WHERE p.PID=pc.PID AND m.MedID=pc.MedID;";
			else if (cat.equals("Studio"))
				query = "CALL SearchStudioByName(\""+term+"\");";
			else if (cat.equals("Award"))
				query = "CALL SearchAwardByname(\""+term+"\");";
			else
				throw new Exception("ERROR: Invalid search category.");
				
			rs = stmt.executeQuery(query);
			rsmd = rs.getMetaData();
			
			noCols = rsmd.getColumnCount();
			
			//the following html statements and loops displays the search results
			%>
			<table BORDER="1">
			<tr>
			<%	for (int i = 1; i <= noCols; i++) //prints out the names of the columns using rsmd
				{%>
					<th><%= rsmd.getColumnName(i) + "\t\t" %></th> 
			<%	}	%>
			</tr>
			<%	int count = 0;
			while (rs.next()) 
			{ %> 
			<tr> 
				<%	for (int i = 1; i <= noCols; i++) //for each row of rs, prints out all column values
					{%>
						<td><%= rs.getString(i) + "\t\t" %></td> 
				<%	}	%>
			</tr> 
			
			<%
			}
			%></table><%
			rs.close(); //closes the result set
		}
		//handles the 5 complex queries
		else if (searchType.length() == 2)
		{
			if (searchType.equals("c1"))
			{
				String minMovies = request.getParameter("minMovies");
				String minTV = request.getParameter("minTV");
				String minRadio = request.getParameter("minRadio");
				query = "SELECT p.Name FROM PERSON AS p,(SELECT pc.PID FROM MEDIA AS m, PLAYEDCHARACTER AS pc WHERE m.Type=\"movie\" AND pc.MedID=m.MedID GROUP BY pc.PID HAVING COUNT(*)>="+minMovies+" UNION ALL SELECT pc.PID FROM MEDIA AS m, PLAYEDCHARACTER AS pc WHERE m.Type=\"tvshow\" AND pc.MedID=m.MedID GROUP BY pc.PID HAVING COUNT(*)>="+minTV+" UNION ALL SELECT pc.PID FROM MEDIA AS m, PLAYEDCHARACTER AS pc WHERE m.Type=\"radioshow\" AND pc.MedID=m.MedID GROUP BY pc.PID HAVING COUNT(*)>="+minRadio+") AS q WHERE p.PID = q.PID GROUP BY p.PID, p.Name HAVING COUNT(*)>=3;";
			}
			else if (searchType.equals("c2"))
			{
				String age = request.getParameter("age");
				
				query = "SELECT DISTINCT p.Name FROM PERSON AS p, PLAYEDCHARACTER AS pc, DIRECTED AS d, WROTE AS w, PRODUCED AS pr WHERE p.PID=pc.PID AND p.PID=d.PID AND p.PID=w.PID AND p.PID=pr.PID AND p.YrDied IS NOT NULL AND p.YrDied - p.YrBorn > "+age+";";
			}
			else if (searchType.equals("c3"))
			{
				String minNoAwards = request.getParameter("minNoAwards");
				
				query = "SELECT p.Name FROM PERSON AS p, WON AS w, DIRECTED AS d WHERE p.PID=d.PID AND d.MedID=w.MedID GROUP BY p.Name, p.PID HAVING COUNT(*)>"+minNoAwards+";";
			}
			else if (searchType.equals("c4"))
			{
				String dollars = request.getParameter("dollars");
				String company = request.getParameter("company");
				
				query = "SELECT p.Name, SUM(m.Profits) FROM PERSON AS p, MOVIE AS m, WROTE AS w, FINANCED AS f WHERE f.StudioName=\""+company+"\" AND p.PID=w.PID AND w.MedID=m.MedID AND f.MedID=m.MedID GROUP BY p.PID, p.Name HAVING SUM(m.Profits)>"+dollars+";";
			}
			else if (searchType.equals("c5"))
			{
				//query = "SELECT o.ParentCompany FROM OWNEDBY AS o WHERE (SELECT COUNT(*) FROM OWNEDBY AS o2 WHERE o2.ParentCompany=o.ParentCompany) = MAX(SELECT COUNT(o3.Subsidiary) FROM OWNEDBY AS o3 GROUP BY o3.ParentCompany);";
				query = "SELECT DISTINCT o.ParentCompany FROM OWNEDBY o WHERE (SELECT COUNT(*) FROM OWNEDBY o2 WHERE o2.ParentCompany=o.ParentCompany) = (SELECT MAX(tbl.n) FROM (SELECT COUNT(*) AS n FROM OWNEDBY o3 GROUP BY o3.ParentCompany) AS tbl);";
			}
			else
				throw new Exception("ERROR: Invalid query selection.");
				
			rs = stmt.executeQuery(query);
			rsmd = rs.getMetaData();
			
			noCols = rsmd.getColumnCount();
			
			//the following html statements and loops displays the search results
			%>
			<table BORDER="1">
			<tr>
			<%	for (int i = 1; i <= noCols; i++) //prints out the names of the columns using rsmd
				{%>
					<th><%= rsmd.getColumnName(i) + "\t\t" %></th> 
			<%	}	%>
			</tr>
			<%	int count = 0;
			while (rs.next()) 
			{ %> 
			<tr> 
				<%	for (int i = 1; i <= noCols; i++) //for each row of rs, prints out all column values
					{%>
						<td><%= rs.getString(i) + "\t\t" %></td> 
				<%	}	%>
			</tr> 
			
			<%
			}
			%></table><%
			rs.close(); //closes the result set
		}
		else //handles the refined search
		{
			if (searchType.equals("person"))
			{	
				String name = request.getParameter("name");
				String yrBorn = request.getParameter("yrBorn");
				String yrDied = request.getParameter("yrDied");
				
				int num = 0;
				
				String start = "SELECT * FROM (";
				
				//these if blocks (along with the similar ones for the following categories)
				//build up the query string and handle the appropriate intersections				
				if (!name.equals(""))
				{
					query += "(SELECT P.Name, P.YrBorn, P.YrDied FROM PERSON P WHERE P.Name = \""+name+"\")";
					num++;
				}
				if (!yrBorn.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT P.Name, P.YrBorn, P.YrDied FROM PERSON P WHERE P.YrBorn = "+yrBorn+")";
					num++;
				}
				if (!yrDied.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT P.Name, P.YrBorn, P.YrDied FROM PERSON P WHERE P.YrDied = "+yrDied+")";
					num++;
				}
				
				query = start + query;
				
				if (num == 0)
					throw new Exception("ERROR: All search fields empty.  Please enter at least one search term.");
				else if (num == 1)
					query += "AS s);";
				else
					query += ") AS q GROUP BY q.Name, q.YrBorn, q.YrDied HAVING COUNT(*) = "+num+";";
			}
			else if (searchType.equals("movie"))
			{
				String title = request.getParameter("title");
				String yrMade = request.getParameter("yrMade");
				String language = request.getParameter("language");
				String director = request.getParameter("director");
				String writer = request.getParameter("writer");
				String producer = request.getParameter("producer");
				String studio = request.getParameter("studio");
				String actor = request.getParameter("actor");
				String character = request.getParameter("character");
				String award = request.getParameter("award");
				String profits = request.getParameter("profits");
				
				int num = 0;
				
				String start = "SELECT * FROM (";
				
				if (!title.equals(""))
				{
					query += "(SELECT med.Title, med.YrMade, med.Lang, mov.Profits FROM (SELECT * FROM MEDIA M WHERE M.Title = \""+title+"\") AS med, MOVIE AS mov WHERE med.MedID = mov.MedID)";
					num++;
				}
				if (!yrMade.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT med.Title, med.YrMade, med.Lang, mov.Profits FROM (SELECT * FROM MEDIA M WHERE M.YrMade = "+yrMade+") AS med, MOVIE AS mov WHERE med.MedID = mov.MedID)";
					num++;
				}
				if (!language.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT med.Title, med.YrMade, med.Lang, mov.Profits FROM (SELECT * FROM MEDIA M WHERE M.Lang = \""+language+"\") AS med, MOVIE AS mov WHERE med.MedID = mov.MedID)";
					num++;
				}
				if (!director.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT d.MedID FROM PERSON AS p, DIRECTED AS d WHERE p.Name=\""+director+"\" AND p.pID=d.PID)";
					query += "(SELECT med.Title, med.YrMade, med.Lang, mov.Profits FROM "+subquery+" AS sub, MOVIE AS mov, MEDIA AS med WHERE sub.MedID = mov.MedID AND mov.MedID=med.MedID)";
					num++;
				}
				if (!writer.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT w.MedID FROM PERSON AS p, WROTE AS w WHERE p.Name=\""+writer+"\" AND p.pID=w.PID)";
					query += "(SELECT med.Title, med.YrMade, med.Lang, mov.Profits FROM "+subquery+" AS sub, MOVIE AS mov, MEDIA AS med WHERE sub.MedID = mov.MedID AND mov.MedID=med.MedID)";
					num++;
				}
				if (!producer.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT pr.MedID FROM PERSON AS p, PRODUCED AS pr WHERE p.Name=\""+producer+"\" AND p.pID=pr.PID)";
					query += "(SELECT med.Title, med.YrMade, med.Lang, mov.Profits FROM "+subquery+" AS sub, MOVIE AS mov, MEDIA AS med WHERE sub.MedID = mov.MedID AND mov.MedID=med.MedID)";
					num++;
				}
				if (!studio.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT f.MedID FROM FINANCED AS f WHERE f.StudioName=\""+studio+"\")";
					query += "(SELECT med.Title, med.YrMade, med.Lang, mov.Profits FROM "+subquery+" AS sub, MOVIE AS mov, MEDIA AS med WHERE sub.MedID = mov.MedID AND mov.MedID=med.MedID)";
					num++;
				}
				if (!actor.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT pc.MedID FROM PERSON AS p, PLAYEDCHARACTER AS pc WHERE p.Name=\""+actor+"\" AND p.PID=pc.PID)";
					query += "(SELECT med.Title, med.YrMade, med.Lang, mov.Profits FROM "+subquery+" AS sub, MOVIE AS mov, MEDIA AS med WHERE sub.MedID = mov.MedID AND mov.MedID=med.MedID)";
					num++;
				}
				if (!character.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT pc.MedID FROM PLAYEDCHARACTER AS pc WHERE pc.Part=\""+character+"\")";
					query += "(SELECT med.Title, med.YrMade, med.Lang, mov.Profits FROM "+subquery+" AS sub, MOVIE AS mov, MEDIA AS med WHERE sub.MedID = mov.MedID AND mov.MedID=med.MedID)";
					num++;
				}
				if (!award.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT w.MedID FROM WON AS w WHERE w.AwardName=\""+award+"\")";
					query += "(SELECT med.Title, med.YrMade, med.Lang, mov.Profits FROM "+subquery+" AS sub, MOVIE AS mov, MEDIA AS med WHERE sub.MedID = mov.MedID AND mov.MedID=med.MedID)";
					num++;
				}
				if (!profits.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT med.Title, med.YrMade, med.Lang, mov.Profits FROM MOVIE as mov, MEDIA as med WHERE mov.profits="+profits+" AND mov.MedID=med.MedID)";
					num++;
				}
				
				query = start + query;
				
				if (num == 0)
					throw new Exception("ERROR: All search fields empty.  Please enter at least one search term.");
				else if (num == 1)
					query += "AS s);";
				else
					query += ") AS q GROUP BY q.Title, q.YrMade, q.Lang, q.Profits HAVING COUNT(*) = "+num+";";
			}
			else if (searchType.equals("tv"))
			{
				String title = request.getParameter("title");
				String yrMade = request.getParameter("yrMade");
				String yrEnded = request.getParameter("yrEnded");
				String noSeasons = request.getParameter("noSeasons");
				String network = request.getParameter("network");
				String language = request.getParameter("language");
				String director = request.getParameter("director");
				String writer = request.getParameter("writer");
				String producer = request.getParameter("producer");
				String studio = request.getParameter("studio");
				String actor = request.getParameter("actor");
				String character = request.getParameter("character");
				String award = request.getParameter("award");
				
				int num = 0;
				
				String start = "SELECT * FROM (";
								
				if (!title.equals(""))
				{
					query += "(SELECT med.Title, med.YrMade, tv.YrEnded, tv.NoSeasons, tv.Network, med.Lang FROM (SELECT * FROM MEDIA M WHERE M.Title = \""+title+"\") AS med, TVSHOW AS tv WHERE med.MedID = tv.MedID)";
					num++;
				}
				if (!yrMade.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT med.Title, med.YrMade, tv.YrEnded, tv.NoSeasons, tv.Network, med.Lang FROM (SELECT * FROM MEDIA M WHERE M.YrMade = "+yrMade+") AS med, TVSHOW AS tv WHERE med.MedID = tv.MedID)";
					num++;
				}
				if (!yrEnded.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT med.Title, med.YrMade, tv.YrEnded, tv.NoSeasons, tv.Network, med.Lang FROM TVSHOW as tv, MEDIA as med WHERE tv.YrEnded="+yrEnded+" AND tv.MedID=med.MedID)";
					num++;
				}
				if (!noSeasons.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT med.Title, med.YrMade, tv.YrEnded, tv.NoSeasons, tv.Network, med.Lang FROM TVSHOW as tv, MEDIA as med WHERE tv.NoSeasons="+noSeasons+" AND tv.MedID=med.MedID)";
					num++;
				}
				if (!network.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT med.Title, med.YrMade, tv.YrEnded, tv.NoSeasons, tv.Network, med.Lang FROM TVSHOW as tv, MEDIA as med WHERE tv.Network=\""+network+"\" AND tv.MedID=med.MedID)";
					num++;
				}
				if (!language.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT med.Title, med.YrMade, tv.YrEnded, tv.NoSeasons, tv.Network, med.Lang FROM (SELECT * FROM MEDIA M WHERE M.Lang = \""+language+"\") AS med, TVSHOW AS tv WHERE med.MedID = tv.MedID)";
					num++;
				}
				if (!director.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT d.MedID FROM PERSON AS p, DIRECTED AS d WHERE p.Name=\""+director+"\" AND p.pID=d.PID)";
					query += "(SELECT med.Title, med.YrMade, tv.YrEnded, tv.NoSeasons, tv.Network, med.Lang FROM "+subquery+" AS sub, TVSHOW AS tv, MEDIA AS med WHERE sub.MedID = tv.MedID AND tv.MedID=med.MedID)";
					num++;
				}
				if (!writer.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT w.MedID FROM PERSON AS p, WROTE AS w WHERE p.Name=\""+writer+"\" AND p.pID=w.PID)";
					query += "(SELECT med.Title, med.YrMade, tv.YrEnded, tv.NoSeasons, tv.Network, med.Lang FROM "+subquery+" AS sub, TVSHOW AS tv, MEDIA AS med WHERE sub.MedID = tv.MedID AND tv.MedID=med.MedID)";
					num++;
				}
				if (!producer.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT pr.MedID FROM PERSON AS p, PRODUCED AS pr WHERE p.Name=\""+producer+"\" AND p.pID=pr.PID)";
					query += "(SELECT med.Title, med.YrMade, tv.YrEnded, tv.NoSeasons, tv.Network, med.Lang FROM "+subquery+" AS sub, TVSHOW AS tv, MEDIA AS med WHERE sub.MedID = tv.MedID AND tv.MedID=med.MedID)";
					num++;
				}
				if (!studio.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT f.MedID FROM FINANCED AS f WHERE f.StudioName=\""+studio+"\")";
					query += "(SELECT med.Title, med.YrMade, tv.YrEnded, tv.NoSeasons, tv.Network, med.Lang FROM "+subquery+" AS sub, TVSHOW AS tv, MEDIA AS med WHERE sub.MedID = tv.MedID AND tv.MedID=med.MedID)";
					num++;
				}
				if (!actor.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT pc.MedID FROM PERSON AS p, PLAYEDCHARACTER AS pc WHERE p.Name=\""+actor+"\" AND p.PID=pc.PID)";
					query += "(SELECT med.Title, med.YrMade, tv.YrEnded, tv.NoSeasons, tv.Network, med.Lang FROM "+subquery+" AS sub, TVSHOW AS tv, MEDIA AS med WHERE sub.MedID = tv.MedID AND tv.MedID=med.MedID)";
					num++;
				}
				if (!character.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT pc.MedID FROM PLAYEDCHARACTER AS pc WHERE pc.Part=\""+character+"\")";
					query += "(SELECT med.Title, med.YrMade, tv.YrEnded, tv.NoSeasons, tv.Network, med.Lang FROM "+subquery+" AS sub, TVSHOW AS tv, MEDIA AS med WHERE sub.MedID = tv.MedID AND tv.MedID=med.MedID)";
					num++;
				}
				if (!award.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT w.MedID FROM WON AS w WHERE w.AwardName=\""+award+"\")";
					query += "(SELECT med.Title, med.YrMade, tv.YrEnded, tv.NoSeasons, tv.Network, med.Lang FROM "+subquery+" AS sub, TVSHOW AS tv, MEDIA AS med WHERE sub.MedID = tv.MedID AND tv.MedID=med.MedID)";
					num++;
				}
				
				query = start + query;
				
				if (num == 0)
					throw new Exception("ERROR: All search fields empty.  Please enter at least one search term.");
				else if (num == 1)
					query += "AS s);";
				else
					query += ") AS q GROUP BY q.Title, q.YrMade, q.YrEnded, q.NoSeasons, q.Network, q.Lang HAVING COUNT(*) = "+num+";";
			}
			else if (searchType.equals("radio"))
			{
				String title = request.getParameter("title");
				String yrMade = request.getParameter("yrMade");
				String yrEnded = request.getParameter("yrEnded");
				String station = request.getParameter("station");
				String language = request.getParameter("language");
				String director = request.getParameter("director");
				String writer = request.getParameter("writer");
				String producer = request.getParameter("producer");
				String studio = request.getParameter("studio");
				String actor = request.getParameter("actor");
				String character = request.getParameter("character");
				String award = request.getParameter("award");
								
				int num = 0;
				
				String start = "SELECT * FROM (";
								
				if (!title.equals(""))
				{
					query += "(SELECT med.Title, med.YrMade, rad.YrEnded, rad.Station, med.Lang FROM (SELECT * FROM MEDIA M WHERE M.Title = \""+title+"\") AS med, RADIOSHOW AS rad WHERE med.MedID = rad.MedID)";
					num++;
				}
				if (!yrMade.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT med.Title, med.YrMade, rad.YrEnded, rad.Station, med.Lang FROM (SELECT * FROM MEDIA M WHERE M.YrMade = "+yrMade+") AS med, RADIOSHOW AS rad WHERE med.MedID = rad.MedID)";
					num++;
				}
				if (!yrEnded.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT med.Title, med.YrMade, rad.YrEnded, rad.Station, med.Lang FROM RADIOSHOW as rad, MEDIA as med WHERE rad.YrEnded="+yrEnded+" AND rad.MedID=med.MedID)";
					num++;
				}
				if (!station.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT med.Title, med.YrMade, rad.YrEnded, rad.Station, med.Lang FROM RADIOSHOW as rad, MEDIA as med WHERE rad.Station=\""+station+"\" AND rad.MedID=med.MedID)";
					num++;
				}
				if (!language.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT med.Title, med.YrMade, rad.YrEnded, rad.Station, med.Lang FROM (SELECT * FROM MEDIA M WHERE M.Lang = \""+language+"\") AS med, RADIOSHOW AS rad WHERE med.MedID = rad.MedID)";
					num++;
				}
				if (!director.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT d.MedID FROM PERSON AS p, DIRECTED AS d WHERE p.Name=\""+director+"\" AND p.pID=d.PID)";
					query += "(SELECT med.Title, med.YrMade, rad.YrEnded, rad.Station, med.Lang FROM "+subquery+" AS sub, RADIOSHOW AS rad, MEDIA AS med WHERE sub.MedID = rad.MedID AND rad.MedID=med.MedID)";
					num++;
				}
				if (!writer.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT w.MedID FROM PERSON AS p, WROTE AS w WHERE p.Name=\""+writer+"\" AND p.pID=w.PID)";
					query += "(SELECT med.Title, med.YrMade, rad.YrEnded, rad.Station, med.Lang FROM "+subquery+" AS sub, RADIOSHOW AS rad, MEDIA AS med WHERE sub.MedID = rad.MedID AND rad.MedID=med.MedID)";
					num++;
				}
				if (!producer.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT pr.MedID FROM PERSON AS p, PRODUCED AS pr WHERE p.Name=\""+producer+"\" AND p.pID=pr.PID)";
					query += "(SELECT med.Title, med.YrMade, rad.YrEnded, rad.Station, med.Lang FROM "+subquery+" AS sub, RADIOSHOW AS rad, MEDIA AS med WHERE sub.MedID = rad.MedID AND rad.MedID=med.MedID)";
					num++;
				}
				if (!studio.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT f.MedID FROM FINANCED AS f WHERE f.StudioName=\""+studio+"\")";
					query += "(SELECT med.Title, med.YrMade, rad.YrEnded, rad.Station, med.Lang FROM "+subquery+" AS sub, RADIOSHOW AS rad, MEDIA AS med WHERE sub.MedID = rad.MedID AND rad.MedID=med.MedID)";
					num++;
				}
				if (!actor.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT pc.MedID FROM PERSON AS p, PLAYEDCHARACTER AS pc WHERE p.Name=\""+actor+"\" AND p.PID=pc.PID)";
					query += "(SELECT med.Title, med.YrMade, rad.YrEnded, rad.Station, med.Lang FROM "+subquery+" AS sub, RADIOSHOW AS rad, MEDIA AS med WHERE sub.MedID = rad.MedID AND rad.MedID=med.MedID)";
					num++;
				}
				if (!character.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT pc.MedID FROM PLAYEDCHARACTER AS pc WHERE pc.Part=\""+character+"\")";
					query += "(SELECT med.Title, med.YrMade, rad.YrEnded, rad.Station, med.Lang FROM "+subquery+" AS sub, RADIOSHOW AS rad, MEDIA AS med WHERE sub.MedID = rad.MedID AND rad.MedID=med.MedID)";
					num++;
				}
				if (!award.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT w.MedID FROM WON AS w WHERE w.AwardName=\""+award+"\")";
					query += "(SELECT med.Title, med.YrMade, rad.YrEnded, rad.Station, med.Lang FROM "+subquery+" AS sub, RADIOSHOW AS rad, MEDIA AS med WHERE sub.MedID = rad.MedID AND rad.MedID=med.MedID)";
					num++;
				}
				
				query = start + query;
				
				if (num == 0)
					throw new Exception("ERROR: All search fields empty.  Please enter at least one search term.");
				else if (num == 1)
					query += "AS s);";
				else
					query += ") AS q GROUP BY q.Title, q.YrMade, q.YrEnded, q.Station, q.Lang HAVING COUNT(*) = "+num+";";
			}
			else if (searchType.equals("character"))
			{
				String role = request.getParameter("role");
				String actor = request.getParameter("actor");
				String media = request.getParameter("media");
				
				int num = 0;
				
				String start = "SELECT * FROM (";
				
				if (!role.equals(""))
				{
					query += "(SELECT p.Name, med.Title, pc.Part FROM PLAYEDCHARACTER AS pc, PERSON AS p, MEDIA AS med WHERE pc.Part=\""+role+"\" AND pc.PID=p.PID AND pc.MedID=med.MedID)";
					num++;
				}
				if (!actor.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT p.Name, med.Title, pc.Part FROM PERSON AS p, PLAYEDCHARACTER AS pc, MEDIA AS med WHERE p.Name=\""+actor+"\" AND p.PID = pc.PID AND pc.MedID=med.MedID)";
					num++;
				}
				if (!media.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					String subquery = "(SELECT MedID FROM SearchPersonByName(\""+media+"\"))";
					query += "(SELECT p.Name, med.Title, pc.Part FROM PERSON AS p, PLAYEDCHARACTER AS pc, MEDIA AS med WHERE med.Title=\""+media+"\" AND p.PID=pc.PID AND med.MedID = pc.MedID)";
					num++;
				}
				
				query = start + query;
				
				if (num == 0)
					throw new Exception("ERROR: All search fields empty.  Please enter at least one search term.");
				else if (num == 1)
					query += "AS s);";
				else
					query += ") AS q GROUP BY q.Name, q.Title, q.Part HAVING COUNT(*) = "+num+";";
			}
			else if (searchType.equals("studio"))
			{
				String name = request.getParameter("name");
				String yrFounded = request.getParameter("yrFounded");
				String ownedBy = request.getParameter("ownedBy");
				String owns = request.getParameter("owns");	
				String financed = request.getParameter("financed");
				
				int num = 0;
				
				String start = "SELECT * FROM (";
				
				if (!name.equals(""))
				{
					query += "(SELECT s.Name, s.YrFounded FROM STUDIO AS s WHERE s.Name=\""+name+"\")";
					num++;
				}
				if (!yrFounded.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT s.Name, s.YrFounded FROM STUDIO AS s WHERE s.YrFounded="+yrFounded+")";
					num++;
				}
				if (!ownedBy.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT s.Name, s.YrFounded FROM STUDIO AS s, OWNEDBY AS o WHERE o.ParentCompany=\""+ownedBy+"\" AND o.Subsidiary=s.Name)";
					num++;
				}
				if (!owns.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT s.Name, s.YrFounded FROM STUDIO AS s, OWNEDBY AS o WHERE s.Name=o.ParentCompany AND o.Subsidiary=\""+owns+"\")";
					num++;
				}
				if (!financed.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT s.Name, s.YrFounded FROM STUDIO AS s, FINANCED AS f, MEDIA AS med WHERE f.StudioName=s.Name AND f.MedID=med.MedID AND med.Title=\""+financed+"\")";
					num++;
				}
				
				query = start + query;
				
				if (num == 0)
					throw new Exception("ERROR: All search fields empty.  Please enter at least one search term.");
				else if (num == 1)
					query += "AS s);";
				else
					query += ") AS q GROUP BY q.Name, q.YrFounded HAVING COUNT(*) = "+num+";";
			}
			else if (searchType.equals("award"))
			{
				String name = request.getParameter("name");
				String description = request.getParameter("description");
				String yrWon = request.getParameter("yrWon");
				String pWinner = request.getParameter("pWinner");
				String mWinner = request.getParameter("mWinner");
				
				int num = 0;
				
				String start = "SELECT * FROM (";
				
				if (!name.equals(""))
				{
					query += "(SELECT DISTINCT a.Name, a.Description FROM AWARD AS a WHERE a.Name=\""+name+"\")";
					num++;
				}
				if (!description.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT DISTINCT a.Name, a.Description FROM AWARD AS a WHERE a.Description=\""+description+"\")";
					num++;
				}
				if (!yrWon.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT DISTINCT a.Name, a.Description FROM AWARD AS a, WON AS w WHERE w.YearWon="+yrWon+" AND w.AwardName = a.Name)";
					num++;
				}
				if (!pWinner.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT DISTINCT a.Name, a.Description FROM AWARD AS a, WON AS w, PERSON AS p WHERE w.PID=p.PID AND w.AwardName = a.Name AND p.Name=\""+pWinner+"\")";
					num++;
				}
				if (!mWinner.equals(""))
				{
					if (!query.equals("")) query += " UNION ALL ";
					query += "(SELECT DISTINCT a.Name, a.Description FROM AWARD AS a, WON AS w, MEDIA AS med WHERE w.MedID=med.MedID AND w.AwardName = a.Name AND med.Title=\""+mWinner+"\")";
					num++;
				}
				
				query = start + query;
				
				if (num == 0)
					throw new Exception("ERROR: All search fields empty.  Please enter at least one search term.");
				else if (num == 1)
					query += "AS s);";
				else
					query += ") AS q GROUP BY q.Name, q.Description HAVING COUNT(*) = "+num+";";
			}
			else
				throw new Exception("ERROR: Invalid search type.");
				
			rs = stmt.executeQuery(query);
			rsmd = rs.getMetaData();
			
			noCols = rsmd.getColumnCount();
			
			%>
			<table BORDER="1">
			<tr>
			<%	for (int i = 1; i <= noCols; i++) //prints out the names of the columns using rsmd
				{%>
					<th><%= rsmd.getColumnName(i) + "\t\t" %></th> 
			<%	}	%>
			</tr>
			<%	int count = 0;
			while (rs.next()) 
			{ %> 
			<tr> 
				<%	for (int i = 1; i <= noCols; i++) //for each row of rs, prints out all column values
					{%>
						<td><%= rs.getString(i) + "\t\t" %></td> 
				<%	}	%>
			</tr> 
			
			<%
			}
			%></table><%
			rs.close(); //closes the result set
		}
		
		%>
		<BR><BR>
		<!-- links to navigate the site -->
		<table border="3"><tr>
		
		<td><a href="http://localhost:8080/mmdb_home.jsp">Return to simple search</a></td>
		<td><a href="http://localhost:8080/mmdb_complicated.jsp">Select a complicated query</a></td>
		<td><a href="http://localhost:8080/mmdb_option.jsp">Start new refined search</a></td>

		</tr></table>
		<%
		 
	}
	//handles exceptions
	catch(Exception e) //handles & prints exceptions
	{
		out.println(e.toString());
	}
	
	finally
	{
		if (conn != null)
		{
			try
			{
				conn.close(); //closes the connection to the database
			}
			catch (Exception e){}
		}
	}
	 
%>